package com.zhucai.credit.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.RefundOrderEntity;

/**
 * 
 * @author hsg
 * RefundOrder
 */
@Repository
public interface RefundOrderRepository extends
		PagingAndSortingRepository<RefundOrderEntity, Long>,
		JpaSpecificationExecutor<RefundOrderEntity> {


	 @Query(value = "select count(*) from tb_xa_mission_list txm where txm.contract_id=?1 and txm.contract_type=?2 and txm.status<>0 and txm.status<>6",nativeQuery = true)
    public Integer findMissionCountByUnfinished(Integer integer, Integer contractType);

	/**
	 * 根据条件查询申请退款的信息
	 * @param orderNo
	 * @param flowNumber
	 * @param payEnterpriseName
	 * @param refundApplyStartTime
	 * @param refundApplyEndTime
	 * @param productName
	 * @param payStatus
	 * @param payType
	 * @param refundStatus
	 * @param i
	 * @param pageSize
	 * @param supUserList 
	 * @param purUserList 
	 * @return
	 */
	@Query(value = "select txro.order_no,txro.flow_number,txpo.product_name,txro.order_amt," +
			"txro.pay_status,txro.refund_apply_amt,txro.refund_status,txro.refund_apply_time," +
			"txro.refund_amt,txro.id,txro.refund_apply_reason,fpoi.pay_type,txpo.pay_enterprise_name,txro.refund_enterprise_name,txro.refund_time " +
			"from finance_refund_order txro " +
			"left join finance_pay_order_item fpoi on txro.order_id = fpoi.id " +
			"left join finance_pay_order txpo on txro.order_no = txpo.order_no " +
			"left join finance_cr_record fcr on fcr.record_no = txro.order_no " +
			"where 1=1 " +
			"and (?1 is null or txro.order_no like concat ('%',?1,'%') or txro.flow_number like concat ('%',?1,'%')) " +
			"and (?2 is null or txpo.pay_enterprise_name like concat ('%',?2,'%')) " +
			"and (?3 is null or txro.refund_apply_time >= ?3) " +
			"and (?4 is null or txro.refund_apply_time <= ?4) " +
			"and (?5 is null or txpo.product_name = ?5) " +
			"and (?6 is null or fpoi.pay_status = ?6) " +
			"and (?7 is null or fpoi.pay_type = ?7) " +
			"and (txro.refund_status in (?8)) " +
			"and (txro.create_user in (?9)) "+
			"order by txro.refund_apply_time desc " +
			"limit ?10,?11",nativeQuery = true)
	List<Object[]> findRefundOrderRecordList(String orderNo,
											 String payEnterpriseName,
											 Date refundApplyStartTime, 
											 Date refundApplyEndTime,
											 String productName, 
											 String payStatus, 
											 String payType,
											 List<String> refundStatus, List<Long> userList,
											 int i, Integer pageSize);

	/**
	 * 根据条件查询申请退款的记录条数
	 * @param orderNo
	 * @param flowNumber
	 * @param payEnterpriseName
	 * @param refundApplyStartTime
	 * @param refundApplyEndTime
	 * @param productName
	 * @param payStatus
	 * @param payType
	 * @param refundStatus
	 * @param supUserList 
	 * @param purUserList 
	 * @return
	 */
	@Query(value = "select count(1) " +
			"from finance_refund_order txro " +
			"left join finance_pay_order txpo on txro.order_id = txpo.id " +
			"left join finance_pay_order_item fpoi on txro.order_id = fpoi.id " +
			"left join finance_cr_record fcr on fcr.record_no = txro.order_no " +
			"where 1=1 " +
			"and (?1 is null or txro.order_no like concat ('%',?1,'%')  or txro.flow_number like concat ('%',?1,'%')) " +
			"and (?2 is null or txpo.pay_enterprise_name like concat ('%',?2,'%')) " +
			"and (?3 is null or txro.refund_apply_time >= ?3) " +
			"and (?4 is null or txro.refund_apply_time <= ?4) " +
			"and (?5 is null or txpo.product_name = ?5) " +
			"and (?6 is null or fpoi.pay_status = ?6) " +
			"and (?7 is null or fpoi.pay_type = ?7) " +
			"and (txro.refund_status in (?8)) "+
			"and (txro.create_user in (?9)) "
			,nativeQuery = true)
	Integer findRefundOrderRecordCounts(String orderNo,
									    String payEnterpriseName,
									    Date refundApplyStartTime, 
									    Date refundApplyEndTime,
									    String productName, 
									    String payStatus, 
									    String payType,
									    List<String> refundStatus, List<Long> userList);




	





}
